package jp.gr.java_conf.genzo.wifinotify.data;

import jp.gr.java_conf.genzo.wifinotify.WifiNotifyApp;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class LogTracker {

	/** DBHelper */
	private DBHelper mHelper = null;
	/** 保存期間(ms) */
	static final long SAVE_MSEC = 24l * 60l * 60l * 1000l * 300l;// 300日

	/**
	 * コンストラクタ
	 *
	 * @param context
	 *            コンテキスト
	 */
	public LogTracker() {
		mHelper = new DBHelper(WifiNotifyApp.getApp());
	}

	public void close() {

		if (mHelper != null) {
			mHelper.close();
			mHelper = null;
		}
	}

	public Cursor getLog() {
		SQLiteDatabase db = mHelper.getWritableDatabase();

		long exclude = System.currentTimeMillis() - SAVE_MSEC;
		final String del_sql = "DELETE FROM t_log_tracker WHERE insert_time < ?;";

		final String sql = "SELECT _id, ssid, rssi, latitude, longitude, accuracy, insert_time FROM t_log_tracker"
				+ " WHERE insert_time >= ?" + " ORDER BY insert_time desc;";

		Cursor cursor = db.rawQuery(sql,
				new String[] { String.valueOf(exclude) });

		db.beginTransaction();
		try {
			db.execSQL(del_sql, new Object[] { exclude });
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}

		return cursor;
	}

	public Cursor getMapList() {
		SQLiteDatabase db = mHelper.getReadableDatabase();
		long exclude = System.currentTimeMillis() - SAVE_MSEC;

		final String sql = "SELECT ssid, latitude, longitude, max(rssi) FROM t_log_tracker"
				+ " WHERE insert_time >= ?"
				+ " GROUP BY rssi, latitude, longitude"
				+ " ORDER BY insert_time desc;";

		Cursor cursor = db.rawQuery(sql,
				new String[] { String.valueOf(exclude) });
		return cursor;
	}

	public String[] getSSIDList() {
		SQLiteDatabase db = mHelper.getReadableDatabase();

		final String sql = "SELECT ssid FROM t_log_tracker" + " GROUP BY ssid"
				+ " ORDER BY ssid;";

		Cursor c = db.rawQuery(sql, null);
		String[] result = new String[c.getCount()];

		int count = 0;
		boolean eof = c.moveToFirst();
		while (eof) {
			result[count] = c.getString(0);
			count++;
			eof = c.moveToNext();
		}
		c.close();
		close();

		return result;

	}

	public void insert(Object[] args) {

		String ins_sql = "INSERT INTO t_log_tracker(ssid, rssi, latitude, longitude, accuracy, insert_time)"
				+ "VALUES (?, ?, ?, ?, ?, ?);";

		SQLiteDatabase db = mHelper.getWritableDatabase();
		db.beginTransaction();
		try {
			db.execSQL(ins_sql, args);
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}
	}

	public void deleteItems(int[] ids) {

		String del_sql = "DELETE FROM t_log_tracker where _id =?";

		SQLiteDatabase db = mHelper.getWritableDatabase();
		db.beginTransaction();
		try {
			for(int id :ids){
				db.execSQL(del_sql, new Object[]{id});
			}
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}

	}

	static class DBHelper extends SQLiteOpenHelper {

		/** データベース名 */
		private static final String DB_NAME = "TrackerDB";
		/** データベースバージョン */
		private static final int DB_VER = 1;

		/**
		 * コンストラクタ.
		 *
		 * @param context
		 *            コンテキスト。
		 */
		public DBHelper(Context context) {
			super(context, DB_NAME, null, DB_VER);
		}

		/*
		 * (非 Javadoc)
		 *
		 * @see
		 * android.database.sqlite.SQLiteOpenHelper#onCreate(android.database
		 * .sqlite.SQLiteDatabase)
		 */
		@Override
		public void onCreate(SQLiteDatabase db) {
			db.beginTransaction();
			try {
				db.execSQL("CREATE TABLE t_log_tracker(_id INTEGER PRIMARY KEY, ssid TEXT NOT NULL, rssi INTEGER, latitude REAL, longitude REAL, accuracy REAL, insert_time INTEGER);");
				db.setTransactionSuccessful();
			} finally {
				db.endTransaction();
			}
		}

		/*
		 * (非 Javadoc)
		 *
		 * @see
		 * android.database.sqlite.SQLiteOpenHelper#onUpgrade(android.database
		 * .sqlite.SQLiteDatabase, int, int)
		 */
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// 現在Ver1なので不要
		}
	}

	public LogData[] getLogDataList() {
		Cursor c = getLog();
		LogData[] result = new LogData[c.getCount()];
		int count = 0;
		boolean eof = c.moveToFirst();
		while (eof) {
			LogData item = new LogData();
			result[count] = item;
			item.mID = c.getInt(0);
			item.mSSID = c.getString(1);
			item.mRSSI = c.getInt(2);
			item.mLat = c.getDouble(3);
			item.mLon = c.getDouble(4);
			item.mAccuracy = c.getDouble(5);
			item.mTime = c.getLong(6);

			count++;
			eof = c.moveToNext();
		}
		c.close();
		close();
		return result;
	}

}